package cetb.data;

import cetb.domain.Address;
import cetb.domain.Client;
import cetb.domain.Shop;
import cetb.domain.User;
import cetb.utils.UIDGenerator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class JDBCUserDAO implements IUserDAO {
	
	private Connection conn;
	
	private IAddressDAO adao;

	private IShopDAO sdao;
	
	private IClientDAO cdao;
	
	private IOfferDAO odao;
	
	private ICommentDAO comdao;
	
	private IOrderDAO ordao;
	
	private IProductDAO pdao;
	
	
	public JDBCUserDAO() {
        conn = ConnectionManager.getInstance().checkOut();
        adao = new JDBCAddressDAO();
        sdao = new JDBCShopDAO();
        cdao = new JDBCClientDAO();
        odao = new JDBCOfferDAO();
        comdao = new JDBCCommentDAO();
        ordao = new JDBCOrderDAO();
        pdao = new JDBCProductDAO();
    }
	
	public void insertClient(Client c) {    
		
		String uoid=UIDGenerator.getInstance().getKey();
		
		//insert user
		insertUser(c,uoid);
        
		//insert client
        cdao.insert(conn,uoid,c);
	
	}

	public void insertShop(Shop s) {
		
		String uoid=UIDGenerator.getInstance().getKey();
		
		//insert user
		insertUser(s,uoid);
        
		//insert shop
        sdao.insert(conn,uoid,s);
	
	}
	
	public List<User> selectAllUsers() {
		List<User> users = new LinkedList<User>();
		
		users.addAll(selectAllClients());
		users.addAll(selectAllShops());
		
		return users;
	}

	private void insertUser(User u, String uoid){
		
		String sql = "INSERT INTO User ( OID, user, password, name, addressOID, karma) VALUES (?, ?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
        	
        	//insert address
        	String aoid=UIDGenerator.getInstance().getKey();
        	adao.insert(conn,aoid,u.getAddress());
        	
        	//insert user
        	stmt = conn.prepareStatement(sql);
        	
            stmt.clearParameters();        
            stmt.setString(1, uoid);
            stmt.setString(2, u.getUser());
            stmt.setString(3, u.getPassword());
            stmt.setString(4, u.getName());
            stmt.setString(5, aoid);
            stmt.setFloat(6, u.getKarma());
            
            stmt.executeUpdate();
        
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
	
	private List<Client> selectAllClients() {
		List<Client> clients = new LinkedList<Client>();
	    String sql = "SELECT * FROM user, client WHERE user.OID = client.OID";
	    PreparedStatement stmt = null;
	    ResultSet result = null;

	    try {
	    	stmt = conn.prepareStatement(sql);
	        result = stmt.executeQuery();

	        while (result.next()) {
	            Client c = new Client();
	            
	            c.setUser(result.getString("user"));
	    		c.setPassword(result.getString("password"));
	    		c.setName(result.getString("name"));
	    		Address a = adao.select(conn, result.getString("addressOID"));
	    		c.setAddress(a);
	    		c.setKarma(result.getFloat("karma"));
	    		c.setDni(result.getString("dni"));
	    		c.setReceiveOffers(result.getBoolean("receiveOffers"));
	    		c.setVipOffers(odao.selectVIPOffers(result.getString("OID")));
	    		
	    		clients.add(c);
	        }
	        
	    } catch (SQLException e) {
	        System.out.println("Message: " + e.getMessage());
	        System.out.println("SQLState: " + e.getSQLState());
	        System.out.println("ErrorCode: " + e.getErrorCode());
	    } finally {
	        try {
	        	if (result != null) {
	            	result.close();
	            }

	            if (stmt != null) {
	            	stmt.close();
	            }
	        } catch (SQLException e) {
	        
	        }
	    }
	    return clients;    
	}
	
	private List<Shop> selectAllShops() {
		List<Shop> shops = new LinkedList<Shop>();
	    String sql = "SELECT * FROM user, shop WHERE user.OID = shop.OID";
	    PreparedStatement stmt = null;
	    ResultSet result = null;

	    try {
	    	stmt = conn.prepareStatement(sql);
	        result = stmt.executeQuery();

	        while (result.next()) {
	            Shop s = new Shop();
	            
	            s.setUser(result.getString("user"));
	    		s.setPassword(result.getString("password"));
	    		s.setName(result.getString("name"));
	    		Address a = adao.select(conn, result.getString("addressOID"));
	    		s.setAddress(a);
	    		s.setKarma(result.getFloat("karma"));
	    		s.setCif(result.getString("cif"));
	    		s.setCategory(result.getString("category"));
	    		s.setComments(comdao.selectShopComments(conn,result.getString("OID")));
	    		s.setOrders(ordao.selectShopOrders(conn,result.getString("OID")));
	    		s.setProducts(pdao.selectShopProducts(conn,result.getString("OID")));
	    		
	    		
	    		shops.add(s);
	        }
	        
	    } catch (SQLException e) {
	        System.out.println("Message: " + e.getMessage());
	        System.out.println("SQLState: " + e.getSQLState());
	        System.out.println("ErrorCode: " + e.getErrorCode());
	    } finally {
	        try {
	        	if (result != null) {
	            	result.close();
	            }

	            if (stmt != null) {
	            	stmt.close();
	            }
	        } catch (SQLException e) {
	        
	        }
	    }
	    return shops;    
	}

}


